﻿using Archives.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Archives.Common
{
    public class DbUtil
    {
        /// <summary>
        /// 表数据委托方法定义
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public delegate List<object> DataHandler(SqlDataReader reader);
        public delegate string SingleDataHandler(SqlDataReader reader);

        /// 查询第三方表数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="whereClause"></param>
        /// <returns></returns>
        public static IEnumerable<object> ExcuteSqlCommand(string sqlText, DataHandler handler)
        {
            var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString;
            var sqlConnection = new SqlConnection(connectionString);
            var resultList = new List<object>();

            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            // 基本的查询
            cmd.CommandText = sqlText;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = sqlConnection;

            sqlConnection.Open();

            reader = cmd.ExecuteReader();
            // 委托读取数据
            resultList = handler(reader);

            sqlConnection.Close();
            return resultList;
        }

        /// <summary>
        /// 执行SQL语句，没有代理
        /// </summary>
        /// <param name="sqlText"></param>
        public static bool ExcuteSqlCommand(string sqlText)
        {
            try
            {
                var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WhDbContext"].ConnectionString;
                var sqlConnection = new SqlConnection(connectionString);

                SqlCommand cmd = new SqlCommand();
                SqlDataReader reader;

                // 基本的查询
                cmd.CommandText = sqlText;
                cmd.CommandType = CommandType.Text;
                cmd.Connection = sqlConnection;

                sqlConnection.Open();

                reader = cmd.ExecuteReader();


                sqlConnection.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public static void ExecuteProcedure(string procName, Dictionary<string, object> paramDict)
        {
            try
            {
                var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WhDbContext"].ConnectionString;
                var sqlConnection = new SqlConnection(connectionString);

                var command = new SqlCommand();
                command.CommandText = procName;
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnection;

                // 参数赋值
                foreach(KeyValuePair<string, object> item in paramDict)
                {
                    command.Parameters.AddWithValue(item.Key, item.Value);
                }

                sqlConnection.Open();

                command.ExecuteNonQuery();

                sqlConnection.Close();
            }
            catch(Exception e)
            {
                throw new Exception(string.Format("存储过程出错：{0}, 详细信息:{1}", procName, e.Message));
            }
        }

        /*
         * 获取审批监测列表
         */
        public static List<object> DeptMonitorHandle(SqlDataReader reader)
        {
            var ResultList = new List<object>();

            while (reader.Read())
            {
                var MessageId = reader.GetString(0);
                var DocumentNumber = reader.GetString(1);
                var DocumentTitle = reader.GetString(2);
                var Users = reader.GetString(3);
                var CreateTime = reader.GetString(4);
                var DelayTime = reader.GetString(5);
                ResultList.Add(new
                {
                    MessageId = MessageId,
                    DocumentNumber = DocumentNumber,
                    DocumentTitle = DocumentTitle,
                    Users = Users,
                    CreateTime = CreateTime,
                    DelayTime = DelayTime
                });
            }
            return ResultList;
        }

        /*
         * 获取某部门内部审批监测列表
         */
        public static List<object> TheDeptMonitorHandle(SqlDataReader reader)
        {
            var ResultList = new List<object>();
            var ResultDict = new Dictionary<string, MonitorHandle>();

            while (reader.Read())
            {
                var MessageId = reader.GetString(0);
                var DocumentNumber = reader.GetString(1);
                var DocumentTitle = reader.GetString(2);
                var Users = reader.GetString(3);
                var CreateTime = reader.GetString(4);
                var DelayTime = reader.GetString(5);

                if (ResultDict.Keys.Contains(MessageId))
                {
                    // 判断长度
                    MonitorHandle handleModel = ResultDict[MessageId];
                    if(Users.Length > handleModel.Users.Length)
                    {
                        ResultDict[MessageId] = new MonitorHandle()
                        {
                            MessageId = MessageId,
                            DocumentNumber = DocumentNumber,
                            DocumentTitle = DocumentTitle,
                            Users = Users,
                            CreateTime = CreateTime,
                            DelayTime = DelayTime
                        };
                    }
                }
                else
                {
                    ResultDict[MessageId] = new MonitorHandle()
                    {
                        MessageId = MessageId,
                        DocumentNumber = DocumentNumber,
                        DocumentTitle = DocumentTitle,
                        Users = Users,
                        CreateTime = CreateTime,
                        DelayTime = DelayTime
                    };
                }
            }

            foreach(var handle in ResultDict.Values)
            {
                ResultList.Add(handle);
            }

            return ResultList;
        }

        /*
         * 获取领导审批通知列表
         */
        public static List<object> LeaderNotifyHandle(SqlDataReader reader)
        {
            var ResultList = new List<object>();

            while (reader.Read())
            {
                var MessageId = reader.GetString(0);
                var DocumentNumber = reader.GetString(1);
                var DocumentTitle = reader.GetString(2);
                var Users = reader.GetString(3);
                var Opinion = reader.GetString(4);
                var CreateTime = reader.GetString(5);
                ResultList.Add(new
                {
                    MessageId = MessageId,
                    DocumentNumber = DocumentNumber,
                    DocumentTitle = DocumentTitle,
                    Users = Users,
                    Opinion = Opinion,
                    CreateTime = CreateTime
                });
            }
            return ResultList;
        }

        /*
         * 监测数据统计
         */
        public static List<object> DeptStatsticsHandle(SqlDataReader reader)
        {
            var ResultList = new List<object>();

            while(reader.Read())
            {
                var Times = reader.GetInt32(0);
                var ParentDeptName = reader.GetString(1);
                var DeptID = reader.GetString(2);
                var DeptName = reader.GetString(3);

                ResultList.Add(new
                {
                    ParentDeptName = ParentDeptName,
                    DeptID = DeptID,
                    DeptName = DeptName,
                    Times = Times
                });
            }

            return ResultList;
        }
    }
}